using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Runtime.InteropServices;

namespace PCSComMaterials.Inventory.DS
{
	[Guid("0682C27B-F870-4e0a-96FD-75EBFD199F91")]
	public class IV_LotItemDS// //: IObjectDS
	{
		public IV_LotItemDS()
		{
		}

		private const string THIS = "PCSComMaterials.Inventory.DS.IV_LotItemDS";


		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_LotItem
		///    </Description>
		///    <Inputs>
		///        IV_LotItemVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_LotItemVO objObject = (IV_LotItemVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO IV_LotItem("
					+ IV_LotItemTable.RECEIVETIMES_FLD + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ","
					+ IV_LotItemTable.LOT_FLD + ","
					+ IV_LotItemTable.CCNID_FLD + ","
					+ IV_LotItemTable.PRODUCTID_FLD + ")"
					+ "VALUES(?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVETIMES_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVETIMES_FLD].Value = objObject.ReceiveTimes;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVEQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVEQUANTITY_FLD].Value = objObject.ReceiveQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = objObject.ProductID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to delete data from IV_LotItem
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       void
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + IV_LotItemTable.TABLE_NAME + " WHERE  " + "LotItemID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_LotItem
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_LotItemVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotItemVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			DataSet dstPCS = new DataSet();

			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_LotItemTable.LOTITEMID_FLD + ","
					+ IV_LotItemTable.RECEIVETIMES_FLD + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ","
					+ IV_LotItemTable.LOT_FLD + ","
					+ IV_LotItemTable.CCNID_FLD + ","
					+ IV_LotItemTable.PRODUCTID_FLD
					+ " FROM " + IV_LotItemTable.TABLE_NAME
					+ " WHERE " + IV_LotItemTable.LOTITEMID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				IV_LotItemVO objObject = new IV_LotItemVO();

				while (odrPCS.Read())
				{
					objObject.LotItemID = int.Parse(odrPCS[IV_LotItemTable.LOTITEMID_FLD].ToString().Trim());
					objObject.ReceiveTimes = int.Parse(odrPCS[IV_LotItemTable.RECEIVETIMES_FLD].ToString().Trim());
					objObject.ReceiveQuantity = Decimal.Parse(odrPCS[IV_LotItemTable.RECEIVEQUANTITY_FLD].ToString().Trim());
					objObject.Lot = odrPCS[IV_LotItemTable.LOT_FLD].ToString().Trim();
					objObject.CCNID = int.Parse(odrPCS[IV_LotItemTable.CCNID_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[IV_LotItemTable.PRODUCTID_FLD].ToString().Trim());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_LotItem
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_LotItemVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotItemVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(string pstrLot, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_LotItemTable.LOTITEMID_FLD + ","
					+ IV_LotItemTable.RECEIVETIMES_FLD + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ","
					+ IV_LotItemTable.LOT_FLD + ","
					+ IV_LotItemTable.CCNID_FLD + ","
					+ IV_LotItemTable.PRODUCTID_FLD
					+ " FROM " + IV_LotItemTable.TABLE_NAME
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "='" + pstrLot + "'"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "=" + pintCCNID
					+ " AND " + IV_LotItemTable.PRODUCTID_FLD + "=" + pintProductID;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				IV_LotItemVO objObject = new IV_LotItemVO();

				while (odrPCS.Read())
				{
					objObject.LotItemID = int.Parse(odrPCS[IV_LotItemTable.LOTITEMID_FLD].ToString().Trim());
					if (odrPCS[IV_LotItemTable.RECEIVETIMES_FLD] != DBNull.Value)
						objObject.ReceiveTimes = int.Parse(odrPCS[IV_LotItemTable.RECEIVETIMES_FLD].ToString().Trim());
					if (odrPCS[IV_LotItemTable.RECEIVEQUANTITY_FLD] != DBNull.Value)
						objObject.ReceiveQuantity = Decimal.Parse(odrPCS[IV_LotItemTable.RECEIVEQUANTITY_FLD].ToString().Trim());
					objObject.Lot = odrPCS[IV_LotItemTable.LOT_FLD].ToString().Trim();
					objObject.CCNID = int.Parse(odrPCS[IV_LotItemTable.CCNID_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[IV_LotItemTable.PRODUCTID_FLD].ToString().Trim());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_LotItem
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_LotItemVO
		///    </Outputs>
		///    <Returns>
		///       IV_LotItemVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool HasProduct(string pstrLot, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".HasProduct()";
			DataSet dstPCS = new DataSet();

			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT COUNT(*) "
					+ " FROM " + IV_LotItemTable.TABLE_NAME
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "= ?"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "= ?"
					+ " AND " + IV_LotItemTable.PRODUCTID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = pintProductID;


				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (int.Parse(objResult.ToString()) > 0)
					{
						return true;
					}
					else
					{
						return false;
					}
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to check for existing Lot
		///    </Description>
		///    <Inputs>
		///        Lot, CCNID
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       true if existed
		///       false if not
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool HasLot(string pstrLot, int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".HasLot()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT COUNT(*) "
					+ " FROM " + IV_LotItemTable.TABLE_NAME
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "= ?"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (int.Parse(objResult.ToString()) > 0)
					{
						return true;
					}
					else
					{
						return false;
					}
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update ReceiveTimes and Recevie Quantity of item in Lot
		///    </Description>
		///    <Inputs>
		///       string Lot, int CCNID, int ProductID, decimal ReceiveQuantity
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Update(string pstrLot, int pintCCNID, int pintProductID, decimal pdecReceiveQuantity)
		{
			const string METHOD_NAME = THIS + ".Update()";

			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE " + IV_LotItemTable.TABLE_NAME + " SET "
					+ IV_LotItemTable.RECEIVETIMES_FLD + "= " + IV_LotItemTable.RECEIVETIMES_FLD + " + 1, "
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + "= " + IV_LotItemTable.RECEIVEQUANTITY_FLD + " + ?"
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "= ?"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "= ?"
					+ " AND " + IV_LotItemTable.PRODUCTID_FLD + "= ?";


				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVEQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVEQUANTITY_FLD].Value = pdecReceiveQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = pintProductID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_LotItem
		///    </Description>
		///    <Inputs>
		///       IV_LotItemVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			IV_LotItemVO objObject = (IV_LotItemVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_LotItem SET "
					+ IV_LotItemTable.RECEIVETIMES_FLD + "=   ?" + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + "=   ?" + ","
					+ IV_LotItemTable.LOT_FLD + "=   ?" + ","
					+ IV_LotItemTable.CCNID_FLD + "=   ?" + ","
					+ IV_LotItemTable.PRODUCTID_FLD + "=  ?"
					+ " WHERE " + IV_LotItemTable.LOTITEMID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVETIMES_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVETIMES_FLD].Value = objObject.ReceiveTimes;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVEQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVEQUANTITY_FLD].Value = objObject.ReceiveQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOTITEMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.LOTITEMID_FLD].Value = objObject.LotItemID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_LotItem
		///    </Description>
		///    <Inputs>
		///       IV_LotItemVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateReturnedGoods(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".UpdateReturnedGoods()";

			IV_LotItemVO objObject = (IV_LotItemVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_LotItem SET "
					+ IV_LotItemTable.RECEIVETIMES_FLD + "= " + IV_LotItemTable.RECEIVETIMES_FLD + " +  ?" + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + "= " + IV_LotItemTable.RECEIVEQUANTITY_FLD + " +  ?"
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "=   ?"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "=   ?"
					+ " AND " + IV_LotItemTable.PRODUCTID_FLD + "=  ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVETIMES_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVETIMES_FLD].Value = objObject.ReceiveTimes;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RECEIVEQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotItemTable.RECEIVEQUANTITY_FLD].Value = objObject.ReceiveQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_LotItem
		///    </Description>
		///    <Inputs>
		///       IV_LotItemVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateReturnGoodsToVendor(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".UpdateReturnGoodsToVendor()";

			IV_LotItemVO objObject = (IV_LotItemVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_LotItem SET "
					+ IV_LotItemTable.RETURNQUANTITY_FLD + "= ?"
					+ " WHERE " + IV_LotItemTable.LOT_FLD + "=   ?"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "=   ?"
					+ " AND " + IV_LotItemTable.PRODUCTID_FLD + "=  ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.RETURNQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[IV_LotItemTable.RETURNQUANTITY_FLD].Value = objObject.ReturnQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_LotItemTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_LotItemTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_LotItemTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to get all data from IV_LotItem
		///    </Description>
		///    <Inputs>
		///               
		///    </Inputs>
		///    <Outputs>
		///       DataSet
		///    </Outputs>
		///    <Returns>
		///       DataSet
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_LotItemTable.LOTITEMID_FLD + ","
					+ IV_LotItemTable.RECEIVETIMES_FLD + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ","
					+ IV_LotItemTable.LOT_FLD + ","
					+ IV_LotItemTable.CCNID_FLD + ","
					+ IV_LotItemTable.PRODUCTID_FLD
					+ " FROM " + IV_LotItemTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, IV_LotItemTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update a DataSet
		///    </Description>
		///    <Inputs>
		///        DataSet       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ IV_LotItemTable.LOTITEMID_FLD + ","
					+ IV_LotItemTable.RECEIVETIMES_FLD + ","
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ","
					+ IV_LotItemTable.LOT_FLD + ","
					+ IV_LotItemTable.CCNID_FLD + ","
					+ IV_LotItemTable.PRODUCTID_FLD
					+ "  FROM " + IV_LotItemTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, IV_LotItemTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}

				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data for Lot dropdown
		///    </Description>
		///    <Inputs>
		///        int
		///    </Inputs>
		///    <Outputs>
		///       DataTable
		///    </Outputs>
		///    <Returns>
		///       DataTable
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       17-Feb-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataTable GetDataForTrueDBDropDown(int pintProductID)
		{
			const string METHOD_NAME = THIS + ".GetDataForLotDropDown()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_LotItemTable.LOTITEMID_FLD + ","
					+ IV_LotItemTable.LOT_FLD
					+ " FROM " + IV_LotItemTable.TABLE_NAME;
				if (pintProductID != 0)
					strSql += " WHERE " + IV_LotItemTable.PRODUCTID_FLD + "=" + pintProductID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, MST_MasterLocationTable.TABLE_NAME);

				return dstPCS.Tables[MST_MasterLocationTable.TABLE_NAME];
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		/// <summary>
		/// This method uses to get total receive quantity of a Lot
		/// </summary>
		/// <param name="pstrLot">Lot to get quantity</param>
		/// <param name="pintProductID">Product in Lot</param>
		/// <param name="pintCCNID">CCN</param>
		/// <returns>Total receive quantity. -1 if lot is not existed</returns>
		public decimal GetTotalReceiveOfLot(string pstrLot, int pintProductID, int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".GetTotalReceiveOfLot()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SUM("
					+ IV_LotItemTable.RECEIVEQUANTITY_FLD + ")"
					+ " FROM " + IV_LotItemTable.TABLE_NAME
					+ " WHERE " + IV_LotItemTable.PRODUCTID_FLD + "=" + pintProductID
					+ " AND " + IV_LotItemTable.LOT_FLD + "='" + pstrLot + "'"
					+ " AND " + IV_LotItemTable.CCNID_FLD + "=" + pintCCNID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult != null && objResult != DBNull.Value)
					return decimal.Parse(objResult.ToString());
				else
					return decimal.MinusOne;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}